﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class UpperComboBLL

    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);        
       
        public DataTable  ListUpperPosition(int EmpSeq, int EmpTypeSeq, int PosSeq)
        {

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ActiveStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@EmpSeq", EmpSeq);
                dbManager.AddParameters(2, "@PosSeq", PosSeq);
                dbManager.AddParameters(3, "@EmpTypeSeq", EmpTypeSeq);

                string sql, sql2;
                sql = "select concat(np.prefix_name, e.first_name, '  ', e.last_name) as full_name"
                        + ", concat(np2.prefix_name, e2.first_name, '  ', e2.last_name) as full_name2"
                        + ", concat(np3.prefix_name, e3.first_name, '  ', e3.last_name) as full_name3"
                        + ", e.emp_seq, e2.emp_seq as emp_seq2, e3.emp_seq as emp_seq3"
                        + ", p.pos_name, p2.pos_name as pos_name2, p3.pos_name as pos_name3"
                        + ", ex.exec_pos_name, ex2.exec_pos_name as exec_pos_name2, ex3.exec_pos_name as exec_pos_name3"
                        + ", c.class_name, c2.class_name as class_name2, c3.class_name as class_name3"
                    + " from hrm_mp_positions p"
                    + " left join hrm_mp_exec_positions ex on p.exec_pos_seq = ex.exec_pos_seq"
                    + " left join hrm_ps_employees e on p.pos_seq = e.pos_seq"
                    + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"

                    + " left join hrm_mp_positions p2 on p.working_upper_pos_seq = p2.pos_seq"
                    + " left join hrm_mp_exec_positions ex2 on p2.exec_pos_seq = ex2.exec_pos_seq"
                    + " left join hrm_ps_employees e2 on p2.pos_seq = e2.pos_seq"
                    + " left join hrm_mp_classes c2 on e2.class_seq = c2.class_seq"
                    + " left join mas_name_prefixes np2 on e2.name_prefix_seq = np2.name_prefix_seq"

                    + " left join hrm_mp_positions p3 on p2.working_upper_pos_seq = p3.pos_seq"
                    + " left join hrm_mp_exec_positions ex3 on p3.exec_pos_seq = ex3.exec_pos_seq"
                    + " left join hrm_ps_employees e3 on p3.pos_seq = e3.pos_seq"
                    + " left join hrm_mp_classes c3 on e3.class_seq = c3.class_seq"
                    + " left join mas_name_prefixes np3 on e3.name_prefix_seq = np3.name_prefix_seq"

                    + " where p.record_status = @ActiveStatus"
                    + (EmpTypeSeq > 0 ? " and p.emp_type_seq = @EmpTypeSeq" : "")
                    + " and p.pos_seq = ( select working_upper_pos_seq "
                                + " from hrm_mp_positions"
                                + " where pos_seq = (select pos_seq from hrm_ps_employees where emp_seq = @EmpSeq)"
                                + ")";

                sql2 = "select * from("
                    + " select emp_seq, full_name, pos_name, exec_pos_name, class_name from (" + sql + ")t1"
                    + " union all"
                    + " select emp_seq2, full_name2 as full_name"
                        + ", pos_name2 as pos_name, exec_pos_name2 as exec_pos_name"
                        + ", class_name2 as class_name from (" + sql + ")t2"
                    + " union all"
                    + " select emp_seq3, full_name3 as full_name"
                        + ", pos_name3 as pos_name, exec_pos_name3 as exec_pos_name"
                        + ", class_name3 as class_name from (" + sql + ")t3"
                    + ")t"
                    + " where t.full_name is not null";
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql2);
                dt = ds.Tables[0];
                return dt;
            }
            catch
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();  
            }
        }
    }
}
